﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DevDL.UserLimit.IDAL;
using System.Data;
using System.Data.SqlClient;
using DevDL.UserLimit.DBUtility;

namespace DevDL.UserLimit.SQLServerDAL
{
    public class RoleDAL : IRoleDAL
    {
        /// <summary>
        /// 添加角色
        /// </summary>
        /// <param name="role"></param>
        /// <returns></returns>
        public int AddRole(Models.Role role)
        {
            string sql = "INSERT INTO [T_Role]([Name],[Description],[FunctionStr])Values(@RoleName,@Description,@FunctionStr)";
            SqlParameter[] par = new SqlParameter[] {
                new SqlParameter("@RoleName",role.Name),
                new SqlParameter("@Description",role.Descrition),
                new SqlParameter("@FunctionStr",role.FunctionStr)
            };
            return SqlHelper.ExecuteNonQuery(SqlHelper.GetConnSting(), CommandType.Text, sql, par);
        }

        /// <summary>
        /// 获取角色
        /// </summary>
        /// <returns></returns>
        public System.Data.DataSet GetRoles()
        {
            string sql = "SELECT [Id],[Name],[Description],[FunctionStr] FROM [T_Role]";
            DataSet ds = SqlHelper.ExecuteDataset(SqlHelper.GetConnSting(), CommandType.Text, sql);
            return ds;
        }

        /// <summary>
        /// 更新角色
        /// </summary>
        /// <param name="ds"></param>
        /// <param name="tableIndex"></param>
        /// <returns></returns>
        public int UpdateRoleDataSet(DataSet ds, int tableIndex)
        {
            SqlConnection conn = SqlHelper.GetConnection();
            SqlCommand updateCommand = SqlHelper.CreateCommand(conn, "up_UpdateRole", new string[] { "Name", "Description", "FunctionStr", "Id" });
            SqlCommand insertCommand = SqlHelper.CreateCommand(conn, "up_InsertRole", new string[] { "Name", "Description", "FunctionStr" });
            SqlCommand deleteCommand = SqlHelper.CreateCommand(conn, "up_DeleteRole", new string[] { "Id" });
            return SqlHelper.UpdateDataset(insertCommand, deleteCommand, updateCommand, ds, 0);
        }

        /// <summary>
        /// 新增角色
        /// </summary>
        /// <param name="role"></param>
        /// <returns></returns>
        public int Insert(Models.Role role)
        {
            string sql = "INSERT INTO [T_Role]([Name],[Description])VALUES(@Name,@Description)";
            SqlParameter[] pars ={
                new SqlParameter("@Name",role.Name),
                new SqlParameter("@Description",role.Descrition)
            };
            return SqlHelper.ExecuteNonQuery(SqlHelper.GetConnection(), CommandType.Text, sql, pars);
        }

        /// <summary>
        /// 更新角色
        /// </summary>
        /// <param name="role"></param>
        /// <returns></returns>
        public int Update(Models.Role role)
        {
            string sql = "UPDATE [T_Role] SET [Name]=@Name,[Description]=@Description,[FunctionStr]=@FunctionStr WHERE [Id]=@Id";
            SqlParameter[] pars ={
                                    new SqlParameter("@Id",role.Id),
                                    new SqlParameter("@Name",role.Name),
                                    new SqlParameter("@Description",role.Descrition),
                                    new SqlParameter("@FunctionStr",role.FunctionStr)
            };
            return SqlHelper.ExecuteNonQuery(SqlHelper.GetConnection(), CommandType.Text, sql, pars);
        }

        /// <summary>
        /// 删除角色
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int Delete(int id)
        {
            string sql = "DELETE FROM [T_Role] WHERE [Id]=@Id";
            SqlParameter par = new SqlParameter("@Id", id);
            return SqlHelper.ExecuteNonQuery(SqlHelper.GetConnection(), CommandType.Text, sql, par);
        }

        /// <summary>
        /// 更新角色
        /// </summary>
        /// <param name="role"></param>
        /// <returns></returns>
        public int UpdateFunction(Models.Role role)
        {
            string sql = "UPDATE [T_Role] SET [FunctionStr]=@FunctionStr WHERE [Id]=@Id";
            SqlParameter[] pars ={
                                     new SqlParameter("@FunctionStr",role.FunctionStr),
                                     new SqlParameter("@Id",role.Id)
                                };
            return SqlHelper.ExecuteNonQuery(SqlHelper.GetConnection(), CommandType.Text, sql, pars);
        }

        /// <summary>
        /// 根据编号查询功能
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public string GetFunctionById(int id)
        {
            string sql = "SELECT [FunctionStr] FROM [T_Role] WHERE [Id]=@Id";
            SqlParameter par = new SqlParameter("@Id", id);
            return Convert.ToString(SqlHelper.ExecuteScalar(SqlHelper.GetConnection(), CommandType.Text, sql, par));
        }

        /// <summary>
        /// 根据操作员编号查询
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public string GetFunctionByOperatorId(int id)
        {
            string sql = "select FunctionStr from T_Role,Operator WHERE T_Role.Id=Operator.RoleId and Operator.Id=@Id";
            SqlParameter par = new SqlParameter("@Id", id);
            return Convert.ToString(SqlHelper.ExecuteScalar(SqlHelper.GetConnection(), CommandType.Text, sql, par));
        }
    }
}
